package cn.iocoder.yudao.module.lowcode.service.generic;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.StrUtil;
import cn.iocoder.yudao.framework.common.pojo.PageResult;
import cn.iocoder.yudao.module.lowcode.controller.admin.generic.vo.*;
import cn.iocoder.yudao.module.lowcode.core.naming.NamingConverter;
import cn.iocoder.yudao.module.lowcode.dal.dataobject.datasource.LowcodeDataSourceConfigDO;
import cn.iocoder.yudao.module.lowcode.enums.NamingConventionEnum;
import cn.iocoder.yudao.module.lowcode.service.datasource.LowcodeDataSourceConfigService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import org.springframework.validation.annotation.Validated;

import javax.annotation.Resource;
import javax.validation.Valid;
import java.sql.*;
import java.util.*;

import static cn.iocoder.yudao.framework.common.exception.util.ServiceExceptionUtil.exception;
import static cn.iocoder.yudao.module.lowcode.enums.ErrorCodeConstants.*;

/**
 * 通用表格管理 Service 实现类
 *
 * @author 芋道源码
 */
@Service
@Validated
@Slf4j
public class GenericTableServiceImpl implements GenericTableService {

    @Resource
    private LowcodeDataSourceConfigService dataSourceConfigService;
    
    @Resource
    private GenericTableServiceEnhancer tableServiceEnhancer;

    @Override
    public PageResult<GenericTableRespVO> getGenericTablePage(String tableName, GenericTablePageReqVO pageReqVO) {
        // 验证表名
        validateTableName(tableName);
        
        // 获取数据源配置
        LowcodeDataSourceConfigDO dataSource = getDataSourceConfig(pageReqVO.getDataSourceId());
        
        try (Connection connection = createConnection(dataSource)) {
            
            // 构建查询SQL
            String countSql = buildCountSql(tableName, pageReqVO.getSearchParams());
            String dataSql = buildSelectSql(tableName, pageReqVO.getSearchParams(), pageReqVO.getSortingFields());
            
            // 查询总数
            long total = 0;
            try (PreparedStatement countStmt = connection.prepareStatement(countSql)) {
                setSearchParams(countStmt, pageReqVO.getSearchParams(), 1);
                try (ResultSet rs = countStmt.executeQuery()) {
                    if (rs.next()) {
                        total = rs.getLong(1);
                    }
                }
            }
            
            // 查询数据
            List<Map<String, Object>> dataList = new ArrayList<>();
            if (total > 0) {
                // 添加分页
                dataSql += " LIMIT ? OFFSET ?";
                
                try (PreparedStatement dataStmt = connection.prepareStatement(dataSql)) {
                    int paramIndex = setSearchParams(dataStmt, pageReqVO.getSearchParams(), 1);
                    dataStmt.setInt(paramIndex++, pageReqVO.getPageSize());
                    dataStmt.setInt(paramIndex, (pageReqVO.getPageNo() - 1) * pageReqVO.getPageSize());
                    
                    try (ResultSet rs = dataStmt.executeQuery()) {
                        ResultSetMetaData metaData = rs.getMetaData();
                        int columnCount = metaData.getColumnCount();
                        
                        while (rs.next()) {
                            Map<String, Object> row = new HashMap<>();
                            for (int i = 1; i <= columnCount; i++) {
                                String columnName = metaData.getColumnName(i);
                                Object value = rs.getObject(i);
                                row.put(columnName, value);
                            }
                            dataList.add(row);
                        }
                    }
                }
            }
            
            // 转换为响应VO
            final Long finalDataSourceId = pageReqVO.getDataSourceId();
            List<GenericTableRespVO> list = dataList.stream()
                .map(data -> convertToRespVO(data, finalDataSourceId))
                .collect(ArrayList::new, ArrayList::add, ArrayList::addAll);
            
            return new PageResult<>(list, total);
            
        } catch (Exception e) {
            log.error("[getGenericTablePage] 分页查询失败: tableName={}, dataSourceId={}", 
                    tableName, pageReqVO.getDataSourceId(), e);
            throw exception(GENERIC_TABLE_SQL_ERROR, e.getMessage());
        }
    }

    @Override
    public List<GenericTableRespVO> getGenericTableList(String tableName, GenericTableExportReqVO exportReqVO) {
        // 验证表名
        validateTableName(tableName);
        
        // 获取数据源配置
        LowcodeDataSourceConfigDO dataSource = getDataSourceConfig(exportReqVO.getDataSourceId());
        
        try (Connection connection = createConnection(dataSource)) {
            
            // 构建查询SQL
            String dataSql = buildExportSelectSql(tableName, exportReqVO.getSearchParams(), exportReqVO.getSortField(), exportReqVO.getSortOrder());
            
            // 查询数据
            List<Map<String, Object>> dataList = new ArrayList<>();
            try (PreparedStatement dataStmt = connection.prepareStatement(dataSql)) {
                setSearchParams(dataStmt, exportReqVO.getSearchParams(), 1);
                
                try (ResultSet rs = dataStmt.executeQuery()) {
                    ResultSetMetaData metaData = rs.getMetaData();
                    int columnCount = metaData.getColumnCount();
                    
                    while (rs.next()) {
                        Map<String, Object> row = new HashMap<>();
                        for (int i = 1; i <= columnCount; i++) {
                            String columnName = metaData.getColumnName(i);
                            Object value = rs.getObject(i);
                            row.put(columnName, value);
                        }
                        dataList.add(row);
                    }
                }
            }
            
            // 转换为响应VO
            final Long finalDataSourceId = exportReqVO.getDataSourceId();
            return dataList.stream()
                .map(data -> convertToRespVO(data, finalDataSourceId))
                .collect(ArrayList::new, ArrayList::add, ArrayList::addAll);
                
        } catch (Exception e) {
            log.error("[getGenericTableList] 列表查询失败: tableName={}, dataSourceId={}", 
                    tableName, exportReqVO.getDataSourceId(), e);
            throw exception(GENERIC_TABLE_EXPORT_FAILED, e.getMessage());
        }
    }

    @Override
    public Long createGenericTable(String tableName, @Valid GenericTableSaveReqVO createReqVO) {
        // 验证表名
        validateTableName(tableName);
        
        // 获取数据源配置
        LowcodeDataSourceConfigDO dataSource = getDataSourceConfig(createReqVO.getDataSourceId());
        
        // 处理字段名转换（使用新的命名转换系统）
        Map<String, Object> processedData = convertFieldNamesForDatabase(createReqVO.getData(), createReqVO.getDataSourceId());
        log.info("[createGenericTable] 字段名转换: {} -> {}", 
                createReqVO.getData().keySet(), processedData.keySet());
        
        // 验证必填字段
        validateRequiredFields(tableName, dataSource, processedData);
        
        try (Connection connection = createConnection(dataSource)) {
            // 构建插入SQL
            String insertSql = buildInsertSql(tableName, processedData);
            
            try (PreparedStatement stmt = connection.prepareStatement(insertSql, Statement.RETURN_GENERATED_KEYS)) {
                setInsertParams(stmt, processedData);
                
                int result = stmt.executeUpdate();
                if (result == 0) {
                    throw exception(GENERIC_TABLE_SQL_ERROR, "插入记录失败");
                }
                
                // 获取生成的ID
                Long id = null;
                try (ResultSet rs = stmt.getGeneratedKeys()) {
                    if (rs.next()) {
                        id = rs.getLong(1);
                    }
                }
                
                log.info("[createGenericTable] 创建记录成功: tableName={}, id={}, dataSourceId={}", 
                        tableName, id, createReqVO.getDataSourceId());
                
                return id;
            }
            
        } catch (Exception e) {
            log.error("[createGenericTable] 创建记录失败: tableName={}, dataSourceId={}", 
                    tableName, createReqVO.getDataSourceId(), e);
            throw exception(GENERIC_TABLE_SQL_ERROR, e.getMessage());
        }
    }

    @Override
    public void updateGenericTable(String tableName, @Valid GenericTableSaveReqVO updateReqVO) {
        // 验证表名
        validateTableName(tableName);
        
        // 验证记录ID
        if (updateReqVO.getId() == null) {
            throw exception(GENERIC_TABLE_FIELD_REQUIRED, "更新记录时ID不能为空");
        }
        
        // 获取数据源配置
        LowcodeDataSourceConfigDO dataSource = getDataSourceConfig(updateReqVO.getDataSourceId());
        
        // 处理字段名转换（使用新的命名转换系统）
        Map<String, Object> processedData = convertFieldNamesForDatabase(updateReqVO.getData(), updateReqVO.getDataSourceId());
        log.info("[updateGenericTable] 字段名转换: {} -> {}", 
                updateReqVO.getData().keySet(), processedData.keySet());
        
        try (Connection connection = createConnection(dataSource)) {
            // 先检查记录是否存在
            if (!recordExists(connection, tableName, updateReqVO.getId())) {
                throw exception(GENERIC_TABLE_RECORD_NOT_EXISTS);
            }
            
            // 构建更新SQL
            String updateSql = buildUpdateSql(tableName, processedData);
            
            try (PreparedStatement stmt = connection.prepareStatement(updateSql)) {
                int paramIndex = setUpdateParams(stmt, processedData, 1);
                stmt.setLong(paramIndex, updateReqVO.getId());
                
                int updateCount = stmt.executeUpdate();
                if (updateCount == 0) {
                    throw exception(GENERIC_TABLE_RECORD_NOT_EXISTS);
                }
                
                log.info("[updateGenericTable] 更新记录成功: tableName={}, id={}, dataSourceId={}", 
                        tableName, updateReqVO.getId(), updateReqVO.getDataSourceId());
            }
            
        } catch (Exception e) {
            log.error("[updateGenericTable] 更新记录失败: tableName={}, id={}, dataSourceId={}", 
                    tableName, updateReqVO.getId(), updateReqVO.getDataSourceId(), e);
            throw exception(GENERIC_TABLE_SQL_ERROR, e.getMessage());
        }
    }

    @Override
    public void deleteGenericTable(String tableName, Long id, Long dataSourceId) {
        // 验证表名
        validateTableName(tableName);
        
        // 获取数据源配置
        LowcodeDataSourceConfigDO dataSource = getDataSourceConfig(dataSourceId);
        
        try (Connection connection = createConnection(dataSource)) {
            // 先检查记录是否存在
            if (!recordExists(connection, tableName, id)) {
                throw exception(GENERIC_TABLE_RECORD_NOT_EXISTS);
            }
            
            // 执行删除操作
            String deleteSql = "DELETE FROM " + tableName + " WHERE id = ?";
            try (PreparedStatement stmt = connection.prepareStatement(deleteSql)) {
                stmt.setLong(1, id);
                
                int deleteCount = stmt.executeUpdate();
                if (deleteCount == 0) {
                    throw exception(GENERIC_TABLE_RECORD_NOT_EXISTS);
                }
                
                log.info("[deleteGenericTable] 删除记录成功: tableName={}, id={}, dataSourceId={}", 
                        tableName, id, dataSourceId);
            }
            
        } catch (Exception e) {
            log.error("[deleteGenericTable] 删除记录失败: tableName={}, id={}, dataSourceId={}", 
                    tableName, id, dataSourceId, e);
            throw exception(GENERIC_TABLE_SQL_ERROR, e.getMessage());
        }
    }

    @Override
    public void batchDeleteGenericTable(String tableName, List<Long> ids, Long dataSourceId) {
        if (CollUtil.isEmpty(ids)) {
            return;
        }
        
        // 验证表名
        validateTableName(tableName);
        
        // 获取数据源配置
        LowcodeDataSourceConfigDO dataSource = getDataSourceConfig(dataSourceId);
        
        try (Connection connection = createConnection(dataSource)) {
            // 构建批量删除SQL
            StringBuilder deleteSql = new StringBuilder("DELETE FROM ").append(tableName).append(" WHERE id IN (");
            for (int i = 0; i < ids.size(); i++) {
                if (i > 0) {
                    deleteSql.append(",");
                }
                deleteSql.append("?");
            }
            deleteSql.append(")");
            
            try (PreparedStatement stmt = connection.prepareStatement(deleteSql.toString())) {
                for (int i = 0; i < ids.size(); i++) {
                    stmt.setLong(i + 1, ids.get(i));
                }
                
                int deleteCount = stmt.executeUpdate();
                
                log.info("[batchDeleteGenericTable] 批量删除记录成功: tableName={}, deleteCount={}, dataSourceId={}", 
                        tableName, deleteCount, dataSourceId);
            }
            
        } catch (Exception e) {
            log.error("[batchDeleteGenericTable] 批量删除记录失败: tableName={}, ids={}, dataSourceId={}", 
                    tableName, ids, dataSourceId, e);
            throw exception(GENERIC_TABLE_BATCH_OPERATION_FAILED, e.getMessage());
        }
    }

    @Override
    public GenericTableConfigRespVO getTableConfig(String tableName, Long dataSourceId) {
        // 验证表名
        validateTableName(tableName);
        
        // 获取数据源配置
        LowcodeDataSourceConfigDO dataSource = getDataSourceConfig(dataSourceId);
        
        try (Connection connection = createConnection(dataSource)) {
            // 获取表信息
            GenericTableConfigRespVO config = new GenericTableConfigRespVO();
            config.setTableName(tableName);
            config.setDataSourceId(dataSourceId);
            config.setDataSourceName(dataSource.getName());
            
            // 获取完整的表元信息
            fillTableMetadata(config, tableName, connection);
            
            // 获取列信息
            List<GenericTableConfigRespVO.GenericTableColumnConfig> columns = getTableColumns(tableName, dataSource);
            
            // 尝试从数据库中加载已保存的配置（包括列配置和表单配置）
            log.info("[getTableConfig] 开始加载保存的配置: tableName={}, dataSourceId={}", tableName, dataSourceId);
            loadSavedConfigs(config, columns, tableName, dataSourceId);
            log.info("[getTableConfig] 加载保存的配置后，tableForm内容: {}", 
                    config.getTableForm() != null ? (config.getTableForm().length() > 100 ? 
                    config.getTableForm().substring(0, 100) + "..." : config.getTableForm()) : "NULL");
            
            config.setColumns(columns);
            
            // 设置默认业务配置
            config.setBusinessConfig(createDefaultBusinessConfig());
            
            // 如果数据库中没有表单配置，则设置默认表单配置
            if (StrUtil.isEmpty(config.getTableForm())) {
                log.info("[getTableConfig] tableForm为空，设置默认配置: tableName={}", tableName);
                config.setTableForm(createDefaultTableForm());
            } else {
                log.info("[getTableConfig] 使用数据库中的tableForm配置: tableName={}, 长度={}", 
                        tableName, config.getTableForm().length());
            }
            
            return config;
            
        } catch (Exception e) {
            log.error("[getTableConfig] 获取表配置失败: tableName={}, dataSourceId={}", tableName, dataSourceId, e);
            throw exception(GENERIC_TABLE_CONFIG_NOT_FOUND, e.getMessage());
        }
    }

    // 私有辅助方法

    /**
     * 获取数据源配置
     */
    private LowcodeDataSourceConfigDO getDataSourceConfig(Long dataSourceId) {
        LowcodeDataSourceConfigDO dataSource = dataSourceConfigService.getDataSourceConfig(dataSourceId);
        if (dataSource == null) {
            throw exception(DATASOURCE_CONFIG_NOT_EXISTS);
        }
        return dataSource;
    }

    /**
     * 验证表名
     */
    private void validateTableName(String tableName) {
        if (StrUtil.isEmpty(tableName)) {
            throw exception(GENERIC_TABLE_INVALID_NAME, "表名不能为空");
        }
        
        // 简单的SQL注入防护
        if (tableName.contains(" ") || tableName.contains(";") || tableName.contains("--") || 
            tableName.contains("/*") || tableName.contains("*/") || tableName.contains("'") || 
            tableName.contains("\"") || tableName.contains("\\")) {
            throw exception(GENERIC_TABLE_INVALID_NAME, "表名包含非法字符");
        }
        
        // 只允许字母、数字、下划线
        if (!tableName.matches("^[a-zA-Z0-9_]+$")) {
            throw exception(GENERIC_TABLE_INVALID_NAME, "表名只能包含字母、数字和下划线");
        }
    }

    /**
     * 验证记录是否存在
     */
    private boolean recordExists(Connection connection, String tableName, Long id) {
        try {
            String sql = "SELECT COUNT(*) FROM " + tableName + " WHERE id = ?";
            try (PreparedStatement stmt = connection.prepareStatement(sql)) {
                stmt.setLong(1, id);
                try (ResultSet rs = stmt.executeQuery()) {
                    if (rs.next()) {
                        return rs.getInt(1) > 0;
                    }
                }
            }
        } catch (Exception e) {
            log.warn("[recordExists] 检查记录存在性失败: tableName={}, id={}", tableName, id, e);
        }
        return false;
    }

    /**
     * 验证必填字段
     */
    private void validateRequiredFields(String tableName, LowcodeDataSourceConfigDO dataSource, Map<String, Object> data) {
        // 获取表的列信息，验证必填字段
        try {
            List<GenericTableConfigRespVO.GenericTableColumnConfig> columns = getTableColumns(tableName, dataSource);
            for (GenericTableConfigRespVO.GenericTableColumnConfig column : columns) {
                if (Boolean.FALSE.equals(column.getNullable()) && !column.getPrimaryKey()) {
                    Object value = data.get(column.getField());
                    if (value == null || (value instanceof String && StrUtil.isEmpty((String) value))) {
                        throw exception(GENERIC_TABLE_FIELD_REQUIRED, "字段 [" + column.getTitle() + "] 不能为空");
                    }
                }
            }
        } catch (Exception e) {
            log.warn("[validateRequiredFields] 验证必填字段失败: tableName={}", tableName, e);
            // 如果无法获取字段信息，则跳过验证
        }
    }

    /**
     * 获取表注释
     */
    private String getTableComment(String tableName, LowcodeDataSourceConfigDO dataSource) {
        try (Connection connection = DriverManager.getConnection(
                dataSource.getUrl(), dataSource.getUsername(), dataSource.getPassword())) {
            
            DatabaseMetaData metaData = connection.getMetaData();
            String catalog = getCatalogName(connection, dataSource.getType());
            String schema = getSchemaName(connection, dataSource.getType());
            
            try (ResultSet rs = metaData.getTables(catalog, schema, tableName, new String[]{"TABLE"})) {
                if (rs.next()) {
                    return rs.getString("REMARKS");
                }
            }
        } catch (Exception e) {
            log.warn("[getTableComment] 获取表注释失败: tableName={}", tableName, e);
        }
        return null;
    }

    /**
     * 获取表列信息
     */
    private List<GenericTableConfigRespVO.GenericTableColumnConfig> getTableColumns(String tableName, 
                                                                                     LowcodeDataSourceConfigDO dataSource) {
        List<GenericTableConfigRespVO.GenericTableColumnConfig> columns = new ArrayList<>();
        
        try (Connection connection = DriverManager.getConnection(
                dataSource.getUrl(), dataSource.getUsername(), dataSource.getPassword())) {
            
            Class.forName(dataSource.getDriverClassName());
            
            DatabaseMetaData metaData = connection.getMetaData();
            String catalog = getCatalogName(connection, dataSource.getType());
            String schema = getSchemaName(connection, dataSource.getType());
            
            // 获取主键信息
            Set<String> primaryKeys = new HashSet<>();
            try (ResultSet pkRs = metaData.getPrimaryKeys(catalog, schema, tableName)) {
                while (pkRs.next()) {
                    primaryKeys.add(pkRs.getString("COLUMN_NAME"));
                }
            }
            
            // 获取列信息
            try (ResultSet rs = metaData.getColumns(catalog, schema, tableName, "%")) {
                int position = 0;
                while (rs.next()) {
                    GenericTableConfigRespVO.GenericTableColumnConfig column = 
                        new GenericTableConfigRespVO.GenericTableColumnConfig();
                    
                    position++;
                    String columnName = rs.getString("COLUMN_NAME");
                    String columnComment = rs.getString("REMARKS");
                    
                    // 基础字段信息
                    column.setId(System.currentTimeMillis() + position); // 临时ID
                    column.setTableId(System.currentTimeMillis()); // 临时表ID
                    column.setColumnName(columnName);
                    column.setColumnComment(StrUtil.isNotEmpty(columnComment) ? columnComment : columnName);
                    column.setDataType(rs.getString("TYPE_NAME"));
                    column.setJavaField(convertToJavaField(columnName));
                    column.setJavaType(mapJdbcTypeToJavaType(rs.getInt("DATA_TYPE")));
                    column.setOrdinalPosition(position);
                    column.setCreateTime(System.currentTimeMillis());
                    
                    // 数据库约束信息
                    column.setNullable("YES".equals(rs.getString("IS_NULLABLE")));
                    column.setPrimaryKey(primaryKeys.contains(columnName));
                    
                    // 表单控制字段
                    column.setDictType(""); // 默认无字典
                    column.setExample(null);
                    column.setHtmlType(getDefaultHtmlType(column.getJavaType(), column.getPrimaryKey()));
                    
                    // 操作控制字段
                    boolean isPrimaryKey = column.getPrimaryKey();
                    boolean isSystemField = isSystemField(columnName);
                    
                    column.setCreateOperation(!isPrimaryKey && !isSystemField);
                    column.setUpdateOperation(!isPrimaryKey && !isSystemField);
                    column.setListOperation(!isSystemField);
                    column.setListOperationCondition(getDefaultConditionType(column.getJavaType()));
                    column.setListOperationResult(!isSystemField);
                    
                    // 业务配置字段
                    column.setSearchable(!isPrimaryKey && !isSystemField);
                    column.setEditable(!isPrimaryKey && !isSystemField);
                    column.setSortable(true);
                    column.setWidth(getDefaultColumnWidth(column.getJavaType()));
                    
                    columns.add(column);
                }
            }
            
        } catch (Exception e) {
            log.error("[getTableColumns] 获取表列信息失败: tableName={}", tableName, e);
            throw exception(GENERIC_TABLE_CONFIG_NOT_FOUND, "无法获取表结构信息");
        }
        
        return columns;
    }

    /**
     * 获取catalog名称
     */
    private String getCatalogName(Connection connection, String dbType) throws SQLException {
        if ("MySQL".equalsIgnoreCase(dbType) || "SQLServer".equalsIgnoreCase(dbType)) {
            return connection.getCatalog();
        }
        return null;
    }

    /**
     * 获取schema名称
     */
    private String getSchemaName(Connection connection, String dbType) throws SQLException {
        if ("MySQL".equalsIgnoreCase(dbType)) {
            return null;
        } else if ("PostgreSQL".equalsIgnoreCase(dbType) || "OpenGauss".equalsIgnoreCase(dbType)) {
            return connection.getSchema() != null ? connection.getSchema() : "public";
        } else if ("Oracle".equalsIgnoreCase(dbType)) {
            return connection.getMetaData().getUserName();
        } else if ("SQLServer".equalsIgnoreCase(dbType)) {
            return "dbo";
        }
        return null;
    }

    /**
     * JDBC类型到Java类型映射
     */
    private String mapJdbcTypeToJavaType(int jdbcType) {
        switch (jdbcType) {
            case Types.VARCHAR:
            case Types.CHAR:
            case Types.LONGVARCHAR:
            case Types.NVARCHAR:
            case Types.NCHAR:
            case Types.CLOB:
            case Types.NCLOB:
                return "String";
            case Types.INTEGER:
                return "Integer";
            case Types.BIGINT:
                return "Long";
            case Types.DECIMAL:
            case Types.NUMERIC:
                return "BigDecimal";
            case Types.DOUBLE:
                return "Double";
            case Types.FLOAT:
                return "Float";
            case Types.BOOLEAN:
            case Types.BIT:
                return "Boolean";
            case Types.DATE:
                return "LocalDate";
            case Types.TIME:
                return "LocalTime";
            case Types.TIMESTAMP:
                return "LocalDateTime";
            case Types.BLOB:
                return "byte[]";
            default:
                return "Object";
        }
    }

    /**
     * 创建数据源连接
     */
    private Connection createConnection(LowcodeDataSourceConfigDO dataSource) throws SQLException, ClassNotFoundException {
        Class.forName(dataSource.getDriverClassName());
        return DriverManager.getConnection(dataSource.getUrl(), dataSource.getUsername(), dataSource.getPassword());
    }

    /**
     * 构建计数SQL
     */
    private String buildCountSql(String tableName, Map<String, Object> searchParams) {
        StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM ").append(tableName);
        if (CollUtil.isNotEmpty(searchParams)) {
            sql.append(" WHERE ");
            boolean first = true;
            for (String key : searchParams.keySet()) {
                if (!first) {
                    sql.append(" AND ");
                }
                sql.append(key).append(" LIKE ?");
                first = false;
            }
        }
        return sql.toString();
    }

    /**
     * 构建查询SQL
     */
    private String buildSelectSql(String tableName, Map<String, Object> searchParams, 
                                  List<cn.iocoder.yudao.framework.common.pojo.SortingField> sortFields) {
        StringBuilder sql = new StringBuilder("SELECT * FROM ").append(tableName);
        
        if (CollUtil.isNotEmpty(searchParams)) {
            sql.append(" WHERE ");
            boolean first = true;
            for (String key : searchParams.keySet()) {
                if (!first) {
                    sql.append(" AND ");
                }
                sql.append(key).append(" LIKE ?");
                first = false;
            }
        }
        
        // 排序
        if (CollUtil.isNotEmpty(sortFields)) {
            sql.append(" ORDER BY ");
            boolean first = true;
            for (cn.iocoder.yudao.framework.common.pojo.SortingField sortField : sortFields) {
                if (!first) {
                    sql.append(", ");
                }
                sql.append(sortField.getField()).append(" ").append(sortField.getOrder());
                first = false;
            }
        } else {
            sql.append(" ORDER BY id DESC");
        }
        
        return sql.toString();
    }

    /**
     * 设置搜索参数
     */
    private int setSearchParams(PreparedStatement stmt, Map<String, Object> searchParams, int startIndex) throws SQLException {
        int paramIndex = startIndex;
        if (CollUtil.isNotEmpty(searchParams)) {
            for (Object value : searchParams.values()) {
                stmt.setString(paramIndex++, "%" + value + "%");
            }
        }
        return paramIndex;
    }

    /**
     * 构建导出查询SQL
     */
    private String buildExportSelectSql(String tableName, Map<String, Object> searchParams, String sortField, String sortOrder) {
        StringBuilder sql = new StringBuilder("SELECT * FROM ").append(tableName);
        
        if (CollUtil.isNotEmpty(searchParams)) {
            sql.append(" WHERE ");
            boolean first = true;
            for (String key : searchParams.keySet()) {
                if (!first) {
                    sql.append(" AND ");
                }
                sql.append(key).append(" LIKE ?");
                first = false;
            }
        }
        
        // 排序
        if (StrUtil.isNotEmpty(sortField)) {
            sql.append(" ORDER BY ").append(sortField);
            if (StrUtil.isNotEmpty(sortOrder)) {
                sql.append(" ").append(sortOrder);
            }
        } else {
            sql.append(" ORDER BY id DESC");
        }
        
        return sql.toString();
    }

    /**
     * 构建插入SQL
     */
    private String buildInsertSql(String tableName, Map<String, Object> data) {
        StringBuilder sql = new StringBuilder("INSERT INTO ").append(tableName).append(" (");
        StringBuilder values = new StringBuilder("VALUES (");
        
        boolean first = true;
        for (String key : data.keySet()) {
            if (!first) {
                sql.append(", ");
                values.append(", ");
            }
            sql.append(key);
            values.append("?");
            first = false;
        }
        
        sql.append(") ").append(values).append(")");
        return sql.toString();
    }

    /**
     * 构建更新SQL
     */
    private String buildUpdateSql(String tableName, Map<String, Object> data) {
        StringBuilder sql = new StringBuilder("UPDATE ").append(tableName).append(" SET ");
        
        boolean first = true;
        for (String key : data.keySet()) {
            if (!first) {
                sql.append(", ");
            }
            sql.append(key).append(" = ?");
            first = false;
        }
        
        sql.append(" WHERE id = ?");
        return sql.toString();
    }

    /**
     * 设置插入参数
     */
    private void setInsertParams(PreparedStatement stmt, Map<String, Object> data) throws SQLException {
        int paramIndex = 1;
        for (Object value : data.values()) {
            stmt.setObject(paramIndex++, value);
        }
    }

    /**
     * 设置更新参数
     */
    private int setUpdateParams(PreparedStatement stmt, Map<String, Object> data, int startIndex) throws SQLException {
        int paramIndex = startIndex;
        for (Object value : data.values()) {
            stmt.setObject(paramIndex++, value);
        }
        return paramIndex;
    }


    /**
     * 转换为响应VO
     */
    private GenericTableRespVO convertToRespVO(Map<String, Object> data) {
        return convertToRespVO(data, 0L); // 默认使用主数据源
    }
    
    /**
     * 转换为响应VO（带数据源ID）
     */
    private GenericTableRespVO convertToRespVO(Map<String, Object> data, Long dataSourceId) {
        GenericTableRespVO vo = new GenericTableRespVO();
        
        // 创建副本以避免修改原始数据
        Map<String, Object> dataCopy = new HashMap<>(data);
        
        // 提取标准字段
        Object id = dataCopy.remove("id");
        if (id != null) {
            vo.setId(Long.valueOf(id.toString()));
        }
        
        Object createTime = dataCopy.remove("createTime");
        if (createTime instanceof Timestamp) {
            vo.setCreateTime(((Timestamp) createTime).toLocalDateTime());
        }
        
        Object updateTime = dataCopy.remove("updateTime");
        if (updateTime instanceof Timestamp) {
            vo.setUpdateTime(((Timestamp) updateTime).toLocalDateTime());
        }
        
        // 处理剩余的动态数据 - 使用新的命名转换系统
        Map<String, Object> processedData = convertFieldNamesForResponse(dataCopy, dataSourceId);
        vo.setData(processedData);
        
        return vo;
    }
    
    /**
     * 将字段名转换为响应格式（数据库格式 → 前端格式）
     * 
     * @param originalData 原始数据Map
     * @param dataSourceId 数据源ID
     * @return 转换后的数据Map
     */
    private Map<String, Object> convertFieldNamesForResponse(Map<String, Object> originalData, Long dataSourceId) {
        try {
            // 获取数据源的命名格式配置
            NamingConventionEnum databaseFormat = getDatabaseNamingConvention(dataSourceId);
            NamingConventionEnum frontendFormat = getFrontendNamingConvention(dataSourceId);
            
            // 使用新的命名转换系统
            return NamingConverter.convertMapKeys(originalData, databaseFormat, frontendFormat);
            
        } catch (Exception e) {
            log.warn("[convertFieldNamesForResponse] 字段名转换失败，使用默认转换: dataSourceId={}", dataSourceId, e);
            
            // 降级处理：默认数据源使用 snake_case → camelCase，其他数据源不转换
            if (dataSourceId == 0L) {
                return NamingConverter.convertMapKeys(originalData, 
                        NamingConventionEnum.SNAKE_CASE, NamingConventionEnum.CAMEL_CASE);
            } else {
                return originalData;
            }
        }
    }
    
    /**
     * 将字段名转换为数据库格式（前端格式 → 数据库格式）
     * 
     * @param originalData 原始数据Map
     * @param dataSourceId 数据源ID
     * @return 转换后的数据Map
     */
    private Map<String, Object> convertFieldNamesForDatabase(Map<String, Object> originalData, Long dataSourceId) {
        try {
            // 获取数据源的命名格式配置
            NamingConventionEnum frontendFormat = getFrontendNamingConvention(dataSourceId);
            NamingConventionEnum databaseFormat = getDatabaseNamingConvention(dataSourceId);
            
            // 使用新的命名转换系统
            return NamingConverter.convertMapKeys(originalData, frontendFormat, databaseFormat);
            
        } catch (Exception e) {
            log.warn("[convertFieldNamesForDatabase] 字段名转换失败，使用默认转换: dataSourceId={}", dataSourceId, e);
            
            // 降级处理：默认数据源使用 camelCase → snake_case，其他数据源不转换
            if (dataSourceId == 0L) {
                return NamingConverter.convertMapKeys(originalData, 
                        NamingConventionEnum.CAMEL_CASE, NamingConventionEnum.SNAKE_CASE);
            } else {
                return originalData;
            }
        }
    }
    
    /**
     * 获取数据源的数据库字段命名格式
     */
    private NamingConventionEnum getDatabaseNamingConvention(Long dataSourceId) {
        // TODO: 后续可以从配置表中读取具体的命名格式配置
        // 目前使用默认规则：默认数据源使用snake_case，其他数据源智能检测
        if (dataSourceId == 0L) {
            return NamingConventionEnum.SNAKE_CASE; // 默认数据源通常是MySQL，使用snake_case
        } else {
            return NamingConventionEnum.AUTO_DETECT; // 其他数据源使用智能检测
        }
    }
    
    /**
     * 获取数据源的前端接口命名格式
     */
    private NamingConventionEnum getFrontendNamingConvention(Long dataSourceId) {
        // 前端通常统一使用camelCase格式
        return NamingConventionEnum.CAMEL_CASE;
    }

    /**
     * 填充表元数据信息
     */
    private void fillTableMetadata(GenericTableConfigRespVO config, String tableName, Connection connection) {
        try {
            // 这个方法现在主要设置一些默认值，真实的表配置信息会在 loadSavedConfigs 方法中从 infra_codegen_table 加载
            config.setEnabled(true);
            config.setTableType("BASE TABLE");

            // 获取表统计信息（仅适用于 MySQL）
            String databaseType = connection.getMetaData().getDatabaseProductName().toLowerCase();
            if (databaseType.contains("mysql")) {
                fillMysqlTableStats(config, tableName, connection);
            } else {
                // 其他数据库设置默认值
                setDefaultTableStats(config);
            }

            // 获取表注释（从实际表结构获取，作为fallback）
            String tableComment = getTableComment(tableName, connection);
            if (StrUtil.isEmpty(config.getTableComment())) {
                config.setTableComment(StrUtil.isNotEmpty(tableComment) ? tableComment : tableName);
            }

        } catch (SQLException e) {
            log.warn("[fillTableMetadata] 获取表元数据失败: tableName={}", tableName, e);
            setDefaultTableStats(config);
        }
    }

    /**
     * 填充 MySQL 表统计信息
     */
    private void fillMysqlTableStats(GenericTableConfigRespVO config, String tableName, Connection connection) {
        String sql = "SELECT ENGINE, TABLE_COLLATION, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH, AUTO_INCREMENT, " +
                     "CHARACTER_SET_NAME FROM INFORMATION_SCHEMA.TABLES t " +
                     "LEFT JOIN INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY c " +
                     "ON t.TABLE_COLLATION = c.COLLATION_NAME " +
                     "WHERE t.TABLE_SCHEMA = DATABASE() AND t.TABLE_NAME = ?";

        try (PreparedStatement stmt = connection.prepareStatement(sql)) {
            stmt.setString(1, tableName);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    config.setTableEngine(rs.getString("ENGINE"));
                    config.setTableCollation(rs.getString("TABLE_COLLATION"));
                    config.setTableCharset(rs.getString("CHARACTER_SET_NAME"));
                    config.setTableRows(rs.getLong("TABLE_ROWS"));
                    config.setDataLength(rs.getLong("DATA_LENGTH"));
                    config.setIndexLength(rs.getLong("INDEX_LENGTH"));
                    config.setAutoIncrement(rs.getLong("AUTO_INCREMENT"));
                }
            }
        } catch (SQLException e) {
            log.warn("[fillMysqlTableStats] 获取 MySQL 表统计信息失败: tableName={}", tableName, e);
            setDefaultTableStats(config);
        }
    }

    /**
     * 设置默认表统计信息
     */
    private void setDefaultTableStats(GenericTableConfigRespVO config) {
        config.setTableEngine("Unknown");
        config.setTableCharset("utf8mb4");
        config.setTableCollation("utf8mb4_unicode_ci");
        config.setTableRows(0L);
        config.setDataLength(0L);
        config.setIndexLength(0L);
        config.setAutoIncrement(1L);
    }

    /**
     * 获取表注释
     */
    private String getTableComment(String tableName, Connection connection) throws SQLException {
        DatabaseMetaData metaData = connection.getMetaData();
        try (ResultSet rs = metaData.getTables(connection.getCatalog(), null, tableName, null)) {
            if (rs.next()) {
                return rs.getString("REMARKS");
            }
        }
        return null;
    }

    /**
     * 创建默认业务配置
     */
    private GenericTableConfigRespVO.TableBusinessConfig createDefaultBusinessConfig() {
        GenericTableConfigRespVO.TableBusinessConfig config = new GenericTableConfigRespVO.TableBusinessConfig();
        config.setCacheEnabled(true);
        config.setCacheExpireTime(3600);
        config.setDataPermissionEnabled(true);
        config.setOperateLogEnabled(true);
        config.setFieldEncryptEnabled(false);
        config.setDefaultSortField("id");
        config.setDefaultSortDirection("desc");
        config.setDefaultPageSize(10);
        config.setImportEnabled(true);
        config.setExportEnabled(true);
        return config;
    }

    /**
     * 创建默认表单配置
     */
    private String createDefaultTableForm() {
        return "{\n}";
    }

    /**
     * 转换数据库字段名为Java字段名
     */
    private String convertToJavaField(String columnName) {
        if (StrUtil.isEmpty(columnName)) {
            return columnName;
        }
        
        // 将下划线命名转换为驼峰命名
        StringBuilder result = new StringBuilder();
        boolean nextUpperCase = false;
        
        for (char c : columnName.toLowerCase().toCharArray()) {
            if (c == '_') {
                nextUpperCase = true;
            } else {
                if (nextUpperCase) {
                    result.append(Character.toUpperCase(c));
                    nextUpperCase = false;
                } else {
                    result.append(c);
                }
            }
        }
        
        return result.toString();
    }

    /**
     * 判断是否为系统字段
     */
    private boolean isSystemField(String columnName) {
        return "create_time".equals(columnName) || 
               "update_time".equals(columnName) || 
               "creator".equals(columnName) || 
               "updater".equals(columnName) ||
               "deleted".equals(columnName) ||
               "tenant_id".equals(columnName);
    }

    /**
     * 获取默认HTML类型
     */
    private String getDefaultHtmlType(String javaType, boolean isPrimaryKey) {
        if (isPrimaryKey) {
            return "input";
        }
        
        switch (javaType) {
            case "Boolean":
                return "radio";
            case "LocalDateTime":
            case "Date":
                return "datetime";
            case "Integer":
            case "Long":
            case "BigDecimal":
                return "input";
            case "String":
            default:
                return "input";
        }
    }

    /**
     * 获取默认条件类型
     */
    private String getDefaultConditionType(String javaType) {
        switch (javaType) {
            case "String":
                return "LIKE";
            case "LocalDateTime":
            case "Date":
                return "BETWEEN";
            case "Boolean":
            case "Integer":
            case "Long":
            default:
                return "=";
        }
    }

    /**
     * 获取默认列宽度
     */
    private Integer getDefaultColumnWidth(String javaType) {
        switch (javaType) {
            case "Boolean":
                return 80;
            case "LocalDateTime":
            case "Date":
                return 160;
            case "Integer":
            case "Long":
                return 100;
            case "BigDecimal":
                return 120;
            case "String":
            default:
                return 150;
        }
    }

    /**
     * 从数据库中加载已保存的配置（包括表单配置和列配置）
     */
    private void loadSavedConfigs(GenericTableConfigRespVO config, List<GenericTableConfigRespVO.GenericTableColumnConfig> columns, String tableName, Long dataSourceId) {
        log.info("[loadSavedConfigs] 开始加载配置: tableName={}, dataSourceId={}", tableName, dataSourceId);
        
        // 使用主数据源（系统数据源）连接，因为 infra_codegen_table 在主数据库中
        LowcodeDataSourceConfigDO mainDataSource = dataSourceConfigService.getDataSourceConfig(0L);
        if (mainDataSource == null) {
            log.warn("[loadSavedConfigs] 无法获取主数据源配置");
            return;
        }
        
        log.info("[loadSavedConfigs] 使用数据源: url={}, username={}", mainDataSource.getUrl(), mainDataSource.getUsername());
        
        try (Connection connection = DriverManager.getConnection(
                mainDataSource.getUrl(),
                mainDataSource.getUsername(),
                mainDataSource.getPassword())) {
            
            // 查询 infra_codegen_table 表，获取完整的表配置信息（只查询未删除的记录）
            String findTableSql = "SELECT id, data_source_config_id, table_name, table_comment, table_form, " +
                                 "scene, template_type, front_type, module_name, business_name, class_name, class_comment, " +
                                 "author, parent_menu_id, create_time, update_time, creator, updater, remark " +
                                 "FROM infra_codegen_table WHERE table_name = ? AND data_source_config_id = ? AND deleted = 0 LIMIT 1";
            Long tableId = null;
            String savedTableForm = null;
            
            log.info("[loadSavedConfigs] 执行查询SQL: {}, 参数: tableName={}, dataSourceId={}", findTableSql, tableName, dataSourceId);
            
            try (PreparedStatement stmt = connection.prepareStatement(findTableSql)) {
                stmt.setString(1, tableName);
                stmt.setLong(2, dataSourceId);
                try (ResultSet rs = stmt.executeQuery()) {
                    if (rs.next()) {
                        tableId = rs.getLong("id");
                        savedTableForm = rs.getString("table_form");
                        
                        // 设置从数据库获取的真实表配置信息
                        config.setId(tableId);
                        config.setTableComment(rs.getString("table_comment"));
                        config.setRemark(rs.getString("remark"));
                        config.setCreateBy(rs.getString("creator"));
                        config.setUpdateBy(rs.getString("updater"));
                        
                        // 处理时间字段
                        java.sql.Timestamp createTime = rs.getTimestamp("create_time");
                        if (createTime != null) {
                            config.setCreateTime(createTime.getTime());
                        }
                        java.sql.Timestamp updateTime = rs.getTimestamp("update_time");
                        if (updateTime != null) {
                            config.setUpdateTime(updateTime.getTime());
                        }
                        
                        log.info("[loadSavedConfigs] 找到表配置: tableId={}, tableName={}, tableComment={}, tableForm长度={}", 
                                tableId, rs.getString("table_name"), rs.getString("table_comment"),
                                savedTableForm != null ? savedTableForm.length() : 0);
                    } else {
                        log.warn("[loadSavedConfigs] 未找到匹配的记录: tableName={}, dataSourceId={}", tableName, dataSourceId);
                        
                        // 查询所有未删除的记录，看看数据库中到底有什么数据
                        String debugSql = "SELECT table_name, data_source_config_id FROM infra_codegen_table WHERE deleted = 0 LIMIT 5";
                        try (PreparedStatement debugStmt = connection.prepareStatement(debugSql);
                             ResultSet debugRs = debugStmt.executeQuery()) {
                            log.info("[loadSavedConfigs] 数据库中前5条记录:");
                            while (debugRs.next()) {
                                log.info("[loadSavedConfigs] - tableName={}, dataSourceConfigId={}", 
                                        debugRs.getString("table_name"), debugRs.getLong("data_source_config_id"));
                            }
                        }
                    }
                }
            }
            
            if (tableId == null) {
                log.debug("[loadSavedConfigs] 未找到表配置，使用默认值: tableName={}, dataSourceId={}", tableName, dataSourceId);
                // 没有找到表配置，设置一些默认值
                config.setId(System.currentTimeMillis()); // 临时ID
                config.setCreateTime(System.currentTimeMillis());
                config.setUpdateTime(System.currentTimeMillis());
                config.setCreateBy("system");
                config.setUpdateBy("system");
                return; // 没有找到表配置，使用默认值
            }
            
            // 设置表单配置
            if (StrUtil.isNotEmpty(savedTableForm)) {
                config.setTableForm(savedTableForm);
                log.info("[loadSavedConfigs] 成功加载表单配置: tableName={}, savedTableForm长度={}, 前100字符={}", 
                        tableName, savedTableForm.length(), 
                        savedTableForm.length() > 100 ? savedTableForm.substring(0, 100) + "..." : savedTableForm);
            } else {
                log.info("[loadSavedConfigs] savedTableForm为空: tableName={}, savedTableForm={}", tableName, savedTableForm);
            }
            
            // 查询列配置（只查询未删除的记录）
            String columnSql = "SELECT column_name, dict_type, html_type, " +
                              "create_operation, update_operation, list_operation, list_operation_condition, list_operation_result " +
                              "FROM infra_codegen_column WHERE table_id = ? AND deleted = 0";
            
            Map<String, Map<String, Object>> savedConfigs = new HashMap<>();
            
            try (PreparedStatement stmt = connection.prepareStatement(columnSql)) {
                stmt.setLong(1, tableId);
                try (ResultSet rs = stmt.executeQuery()) {
                    while (rs.next()) {
                        String columnName = rs.getString("column_name");
                        Map<String, Object> columnConfig = new HashMap<>();
                        columnConfig.put("dictType", rs.getString("dict_type"));
                        columnConfig.put("htmlType", rs.getString("html_type"));
                        columnConfig.put("createOperation", rs.getBoolean("create_operation"));
                        columnConfig.put("updateOperation", rs.getBoolean("update_operation"));
                        columnConfig.put("listOperation", rs.getBoolean("list_operation"));
                        columnConfig.put("listOperationCondition", rs.getString("list_operation_condition"));
                        columnConfig.put("listOperationResult", rs.getBoolean("list_operation_result"));
                        
                        savedConfigs.put(columnName, columnConfig);
                    }
                }
            }
            
            // 应用已保存的配置到列对象
            for (GenericTableConfigRespVO.GenericTableColumnConfig column : columns) {
                Map<String, Object> savedConfig = savedConfigs.get(column.getColumnName());
                if (savedConfig != null) {
                    column.setDictType((String) savedConfig.get("dictType"));
                    column.setHtmlType((String) savedConfig.get("htmlType"));
                    column.setCreateOperation((Boolean) savedConfig.get("createOperation"));
                    column.setUpdateOperation((Boolean) savedConfig.get("updateOperation"));
                    column.setListOperation((Boolean) savedConfig.get("listOperation"));
                    column.setListOperationCondition((String) savedConfig.get("listOperationCondition"));
                    column.setListOperationResult((Boolean) savedConfig.get("listOperationResult"));
                }
            }
            
            log.debug("[loadSavedConfigs] 成功加载 {} 个列的配置", savedConfigs.size());
            
        } catch (Exception e) {
            log.warn("[loadSavedConfigs] 加载保存的配置失败: tableName={}, dataSourceId={}", tableName, dataSourceId, e);
            // 失败时不影响默认逻辑，继续使用代码生成的默认值
        }
    }

}